﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.Web;

namespace DataService
{
    [DataContract (IsReference=true)]
    public class Utilities
    {
        // Method to get connection string
        public static string GetConnectionString()
        {
            return Properties.Settings.Default.ConnectionString;
        }

        public DataTable ReadData(string StoredProcedure, SqlParameter[] Parameters)
        {
            try
            {
                // Open database connection
                SqlConnection conn = new SqlConnection(GetConnectionString());
                conn.Open();

                // Create command object
                SqlCommand cmd = new SqlCommand(StoredProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // Add parameter list to command object
                foreach (SqlParameter parameter in Parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                // Fill data table
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                conn.Close();
                return dt;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        public void ExecuteProcedure(string StoredProcedure, SqlParameter[] Parameters)
        {
            // Open database connection
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();

            // Create command object
            SqlCommand cmd = new SqlCommand(StoredProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // Add parameter list to command object
            foreach (SqlParameter parameter in Parameters)
            {
                cmd.Parameters.Add(parameter);
            }

            // Execute stored procedure
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}